package cn.kgc.jdbc.test;

import cn.kgc.jdbc.entity.Usr;
import cn.kgc.jdbc.util.DruidUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

/**
 * @author mosin
 * date 2022/2/12
 * @version 1.0
 * 使用 DBUtils 工具类实现数据查询  动态查询  动态sql
 */
public class DBUtilsTest07 {

    public static void main(String[] args) {

        //获取数据库连接池
        DataSource dataSource = DruidUtil.getDataSource();
        // 创建 QueryRunner 的实体类对象 用于执行 数据库的增删改查操作
        QueryRunner runner = new QueryRunner(dataSource);
        /**
         *  sql语句中参数传递是一个对象  usr
         *  根据usr对象中的属性 id  username  查询数据
         *  id不为null username 为空   根据id 查询
         *  id为null username 不为空  根据username查询
         *  id不为null username 不为空  根据  username和id联合查询
         *  id为null username 为空        查询所有数据
         *
         */
        Usr usr = Usr.builder().id(7L).username("李四").build();
        /**
         *  1.select * from  usr where id = ?
         *  2.select * from  usr where username = ?
         *  3.select * from  usr where username = ? and id = ?
         *  4.select * from  usr
         *
         */
        String sql = "select id,username,password,birth, create_time as createTime from usr where 1=1 ";
        ArrayList<Object> params = new ArrayList<>();
        if (usr.getId()!=null) {
            sql+=" and id = ?";
            params.add(usr.getId());
        }

        if(usr.getUsername()!=null){
            sql+=" and username = ?";
            params.add(usr.getUsername());
        }
        System.out.println("sql = " + sql);
        System.out.println("params = " + params.toString());
        try {
            List<Usr> users = runner.query(sql, new BeanListHandler<>(Usr.class),params.toArray());
            for (Usr user : users) {
                System.out.println("user = " + user);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }

}
